from flask import Blueprint, jsonify, request
from work.database import get_db
from datetime import datetime, timedelta

bp = Blueprint('data_api', __name__)


def format_timestamp(ts):
    """统一时间格式"""
    return ts.strftime("%Y-%m-%d %H:%M:%S")


def execute_query(query, params=()):
    """通用数据库查询函数"""
    try:
        conn = get_db()
        if not conn:
            return None, "数据库连接失败"
        with conn.cursor() as cursor:
            cursor.execute(query, params)
            return cursor.fetchall(), None
    except Exception as e:
        return None, str(e)
    finally:
        if conn:
            conn.close()


@bp.route('/latest')
def get_latest():
    result, error = execute_query("""
        SELECT TOP 1 temperature, humidity, record_time 
        FROM SensorData 
        ORDER BY record_time DESC
    """)

    if error:
        return jsonify({"error": error}), 500
    if not result:
        return jsonify({"message": "暂无数据"}), 404

    row = result[0]
    return jsonify({
        "temperature": float(row[0]),
        "humidity": float(row[1]),
        "timestamp": format_timestamp(row[2])
    })


@bp.route('/history')
def get_history():
    limit = request.args.get('limit', default=10, type=int)
    result, error = execute_query("""
        SELECT TOP (?) temperature, humidity, record_time 
        FROM SensorData 
        ORDER BY record_time DESC
    """, (limit,))

    if error:
        return jsonify({"error": error}), 500

    data = [{
        "temperature": float(row[0]),
        "humidity": float(row[1]),
        "timestamp": format_timestamp(row[2])
    } for row in result]

    return jsonify(data)


@bp.route('/stats/hourly')
def get_hourly_stats():
    """当天小时级统计（00:00-23:00）"""
    result, error = execute_query("""
        SELECT DATEPART(HOUR, record_time) AS hour,
               AVG(temperature) AS avg_temp,
               AVG(humidity) AS avg_hum
        FROM SensorData
        WHERE CAST(record_time AS DATE) = CAST(GETDATE() AS DATE)
        GROUP BY DATEPART(HOUR, record_time)
        ORDER BY hour
    """)

    if error:
        return jsonify({"error": error}), 500

    # 只返回存在数据的小时
    hourly_data = [
        {
            "hour": f"{row.hour:02d}",
            "avg_temperature": round(float(row.avg_temp), 1),
            "avg_humidity": round(float(row.avg_hum), 1)
        }
        for row in result if row.avg_temp is not None
    ]

    return jsonify(hourly_data)


@bp.route('/stats/24h_trend')
def stats_24h():
    """24小时趋势（修复时间轴对齐问题）"""
    try:
        # 生成24小时时间窗口（当前时刻整点对齐）
        now = datetime.now().replace(minute=0, second=0, microsecond=0)
        time_windows = [(now - timedelta(hours=i)).strftime('%Y-%m-%d %H:00:00') for i in range(23, -1, -1)]

        # 执行分组查询
        query = """
            SELECT 
                DATEADD(HOUR, DATEDIFF(HOUR, 0, record_time), 0) AS time_window,
                AVG(temperature) AS avg_temp,
                AVG(humidity) AS avg_hum
            FROM SensorData
            WHERE record_time >= DATEADD(HOUR, -23, GETDATE())
            GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, record_time), 0)
            ORDER BY time_window
        """

        conn = get_db()
        with conn.cursor() as cursor:
            cursor.execute(query)
            results = cursor.fetchall()

        # 构建完整数据集
        result_dict = {row.time_window.strftime('%Y-%m-%d %H:00:00'): row for row in results}
        trend_data = []
        for tw in time_windows:
            if tw in result_dict:
                row = result_dict[tw]
                trend_data.append({
                    "hour": datetime.strptime(tw, '%Y-%m-%d %H:%M:%S').strftime('%H'),
                    "temperature": round(float(row.avg_temp), 1) if row.avg_temp else None,
                    "humidity": round(float(row.avg_hum), 1) if row.avg_hum else None
                })
            else:
                trend_data.append({
                    "hour": datetime.strptime(tw, '%Y-%m-%d %H:%M:%S').strftime('%H'),
                    "temperature": None,
                    "humidity": None
                })

        return jsonify(trend_data)

    except Exception as e:
        return jsonify({"error": str(e)}), 500


@bp.route('/stats/7d_trend')
def stats_7d():
    result, error = execute_query("""
        SELECT CONVERT(VARCHAR(10), record_time, 120) AS date,
               AVG(temperature) AS avg_temp,
               AVG(humidity) AS avg_hum
        FROM SensorData
        WHERE record_time >= DATEADD(DAY, -6, CAST(GETDATE() AS DATE))
        GROUP BY CONVERT(VARCHAR(10), record_time, 120)
        ORDER BY date ASC
    """)

    if error:
        return jsonify({"error": error}), 500

    data = [{
        "date": row.date,
        "temperature": round(float(row.avg_temp), 1),
        "humidity": round(float(row.avg_hum), 1)
    } for row in result if row.avg_temp is not None]

    return jsonify(data)